Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic

Public Class D_Cliente

    'agregar un cliente
    Public Function AgregarCliente(ByVal item As Entidades.E_Cliente) As Int32
        'creamos la conexion a utilizar
        Using oCnn As New SqlConnection(StringConexion.CONEXION)
            oCnn.Open()
            Using oCmd As New SqlCommand
                oCmd.Connection = oCnn
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.CommandText = "ClienteAdd"
                oCmd.Parameters.AddWithValue("@nombre", item.Nombre)
                oCmd.Parameters.AddWithValue("@domicilio", item.Domicilio)
                oCmd.Parameters.AddWithValue("@localidad", item.Localidad)
                oCmd.Parameters.AddWithValue("@cuit", item.Cuit)
                Return oCmd.ExecuteNonQuery
            End Using
        End Using
    End Function

    Public Function ModificarCliente(ByVal item As Entidades.E_Cliente) As Int32
        'creamos la conexion a utilizar
        Using oCnn As New SqlConnection(StringConexion.CONEXION)
            oCnn.Open()
            Using oCmd As New SqlCommand
                oCmd.Connection = oCnn
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.CommandText = "ClienteUP"
                oCmd.Parameters.AddWithValue("@nombre", item.Nombre)
                oCmd.Parameters.AddWithValue("@domicilio", item.Domicilio)
                oCmd.Parameters.AddWithValue("@localidad", item.Localidad)
                oCmd.Parameters.AddWithValue("@cuit", item.Cuit)
                Return oCmd.ExecuteNonQuery
            End Using
        End Using
    End Function

    Public Function TraerClientePorNombre(ByVal Nombre As String) As List(Of Entidades.E_Cliente)

        'ENTIDADES.Clientes()
        Using oCnn As New SqlConnection(StringConexion.CONEXION)
            oCnn.Open()
            Using oCmd As New SqlCommand
                oCmd.Connection = oCnn
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.CommandText = "ClienteGetOne"
                oCmd.Parameters.AddWithValue("@nombre", Nombre)
                Dim oclientes As New List(Of Entidades.E_Cliente)
                'ENTIDADES.Clientes()

                Try
                    Using oReader As SqlClient.SqlDataReader = oCmd.ExecuteReader
                        Do While oReader.Read
                            Dim ocliente As New Entidades.E_Cliente

                            ocliente.IDCliente = Convert.ToInt32(oReader("IDCliente"))
                            ocliente.Nombre = oReader("Nombre").ToString
                            ocliente.Localidad = Convert.ToInt32(oReader("Localidad"))
                            ocliente.Domicilio = oReader("Domicilio").ToString
                            ocliente.Cuit = Convert.ToInt64(oReader("Cuit"))
                            oclientes.Add(ocliente)
                            ocliente = Nothing
                        Loop
                        Return oclientes
                    End Using
                Finally
                    oclientes = Nothing
                End Try
            End Using
        End Using
    End Function

    Public Function TraerClientePorCuit(ByVal Cuit As Int64) As List(Of Entidades.E_Cliente)

        'ENTIDADES.Clientes
        Using oCnn As New SqlConnection(StringConexion.CONEXION)
            oCnn.Open()
            Using oCmd As New SqlCommand
                oCmd.Connection = oCnn
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.CommandText = "ClienteGetcuit"
                oCmd.Parameters.AddWithValue("@cuit", Cuit)
                Dim oclientes As New List(Of Entidades.E_Cliente) 'ENTIDADES.Clientes

                Try
                    Using oReader As SqlClient.SqlDataReader = oCmd.ExecuteReader
                        Do While oReader.Read
                            Dim ocliente As New Entidades.E_Cliente

                            ocliente.IDCliente = Convert.ToInt32(oReader("IDCliente"))
                            ocliente.Nombre = oReader("Nombre").ToString
                            ocliente.Localidad = Convert.ToInt32(oReader("Localidad"))
                            ocliente.Domicilio = oReader("Domicilio").ToString
                            ocliente.Cuit = Convert.ToInt64(oReader("Cuit"))
                            oclientes.Add(ocliente)
                            ocliente = Nothing
                        Loop
                        Return oclientes
                    End Using
                Finally
                    oclientes = Nothing
                End Try
            End Using
        End Using
    End Function

    Public Sub BorrarCliente(ByVal cuit As Int64)

        'creamos la conexion a utilizar
        Using oCnn As New SqlConnection(StringConexion.CONEXION)
            oCnn.Open()
            Using oCmd As New SqlCommand
                oCmd.Connection = oCnn
                oCmd.CommandType = CommandType.StoredProcedure
                oCmd.CommandText = "clientedelete"
                oCmd.Parameters.AddWithValue("@cuit", cuit)
                oCmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

End Class
